package com.app.megaprosoft.DB;

import java.io.File;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import com.app.megaprosoft.general.DateTime;
import com.app.megaprosoft.modyimcarcare.Carcare_Login;

import android.app.Application;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Address;
import android.os.Environment;
import android.util.Log;

public class SQLiteDBClass extends SQLiteOpenHelper {

	ResultSet rs = null;
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "MegaAutoCare";
	// User
	private static final String TABLE_FFUSERGROUP = "_FFUSERGROUP";
	private static final String TABLE_FFUSER = "_FFUSER";
	// ArFF
	private static final String TABLE_ArFF = "_ArFF";
	private static final String TABLE_ArGroupMain = "_ArGroupMain";
	private static final String TABLE_ArGroup = "_ArGroup";
	private static final String TABLE_Addresses = "_Addresses";
	// ArFFCar
	private static final String TABLE_CarGroup = "_CarGroup";
	private static final String TABLE_CarBands = "_CarBands";
	private static final String TABLE_CarGenerations = "_CarGenerations";
	private static final String TABLE_Colors = "_Colors";
	// Item
	private static final String TABLE_Item = "_Item";
	private static final String TABLE_ItemGroup = "_ItemGroup";
	private static final String TABLE_ItemGroupMain = "_ItemGroupMain";
	private static final String TABLE_ItemPrice = "_ItemPrice";
	private static final String TABLE_ItemPromotionPrices = "_ItemPromotionPrices";
	private static final String TABLE_ItemSize = "_ItemSize";
	private static final String TABLE_ItemStock = "_ItemStock";
	private static final String TABLE_ItemUnit = "_ItemUnit";
	// Service
	private static final String TABLE_ServicePOS = "_ServicePOS";
	private static final String TABLE_ServicePOSLists = "_ServicePOSLists";

	public SQLiteDBClass(Context context) {
		super(context, Environment.getExternalStorageDirectory()
				+ File.separator + "/DataBase/" + File.separator
				+ DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL("CREATE TABLE " + TABLE_ServicePOSLists
					+ "(ServicePOSListsOID TEXT(100) PRIMARY KEY,"

					+ " POSOID TEXT(100)," 
					+ " ItemOID TEXT(100),"
					+ " Qty TEXT(100)," 
					+ " Price TEXT(100),"
					+ " TotalAmount TEXT(100),"

					+ " Discount TEXT(100)," 
					+ " NetAmount TEXT(100),"
					+ " UnitOID TEXT(100)," 
					+ " WHOID TEXT(100),"
					+ " ICZoneOID TEXT(100),"

					+ " ICStorageOID TEXT(100)," 
					+ " ICShelfOID TEXT(100),"
					+ " ICColumnOID TEXT(100),"
					+ " StkInOutDateTime TEXT(100)," 
					+ " Status TEXT(100),"

					+ " UserModify TEXT(100)," 
					+ " CreateTime TEXT(100),"
					+ " LastUpdate TEXT(100)," 
					+ " StatusUpdate TEXT(100),"
					+ " StatusDoc TEXT(100));");

		} catch (Exception e) {

		}
	
		try {
			db.execSQL("CREATE TABLE " + TABLE_ServicePOS
					+ "(ServicePOSOID TEXT(100) PRIMARY KEY,"

					+ " DocNo TEXT(100)," 
					+ " RefDocNo TEXT(100),"
					+ " ArOID TEXT(100)," 
					+ " UserOID TEXT(100),"
					+ " DocDateTime TEXT(100),"

					+ " PayableDateTime TEXT(100)," 
					+ " CompanyOID TEXT(100),"
					+ " SaleType TEXT(100)," 
					+ " ArType TEXT(100),"
					+ " TotalAmount TEXT(100),"

					+ " DiscountAmount TEXT(100)," 
					+ " ReceiveMoney TEXT(100),"
					+ " NetTotalAmount TEXT(100),"
					+ " DocumentStatus TEXT(100),"
					+ " BalanceStatus TEXT(100),"

					+ " UserModify TEXT(100)," 
					+ " CreateTime TEXT(100),"
					+ " LastUpdate TEXT(100)," 
					+ " StatusUpdate TEXT(100),"
					+ " StatusDoc TEXT(100));");

		} catch (Exception e) {

		}

		// TODO Auto-generated method stub
		try {
			db.execSQL("CREATE TABLE " + TABLE_FFUSERGROUP
					+ "(FFUSERGROUPOID TEXT(100) PRIMARY KEY,"
					+ " UserGroupCode TEXT(100),"
					+ " UserGroupName TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_FFUSER
					+ "(FFUserOID TEXT(100) PRIMARY KEY,"
					+ " LoginName TEXT(100)," + " UserName TEXT(100),"
					+ " Password TEXT(100)," + " DepCode TEXT(100),"
					+ " DepName TEXT(100)," + " UserGroupCode TEXT(100),"
					+ " UserGroupName TEXT(100)," + " PicPath TEXT(100));");
		} catch (Exception e) {

		}
		// กลุ่มลูกค้า
		try {
			db.execSQL("CREATE TABLE " + TABLE_ArFF
					+ "(ArFFOID TEXT(100) PRIMARY KEY,"

					+ " ArCode TEXT(100)," 
					+ " ArTitle TEXT(100),"
					+ " TitleName TEXT(100)," 
					+ " ArName TEXT(100),"
					+ " ArNickName TEXT(100),"

					+ " ArAddr1 TEXT(100)," 
					+ " ArAddr2 TEXT(100),"
					+ " TumbolCode TEXT(100),"
					+ " TumbolName TEXT(100),"
					+ " AmpurCode TEXT(100),"

					+ " AmpurName TEXT(100)," 
					+ " ProvinceCode TEXT(100),"
					+ " ProvinceName TEXT(100)," 
					+ " BillAdd1 TEXT(100),"
					+ " BillAdd2 TEXT(100),"

					+ " Telephone TEXT(100)," 
					+ " Fax TEXT(100),"
					+ " Mobile TEXT(100)," 
					+ " ArGroupOID TEXT(100),"
					+ " ArGroupCode TEXT(100),"

					+ " ArGroupDesc TEXT(100)," 
					+ " ArGroupMainOID TEXT(100),"
					+ " ArGroupMainCode TEXT(100),"
					+ " ArGroupMainDesc TEXT(100),"
					+ " IsDisplayWeightDoc TEXT(100),"

					+ " ContactDate TEXT(100)," 
					+ " tmpContactDate TEXT(100),"
					+ " FFSaleCode TEXT(100)," 
					+ " SaleName TEXT(100),"
					+ " FFUserCode TEXT(100),"

					+ " CreatedBy TEXT(100)," 
					+ " CreditDay TEXT(100),"
					+ " Remark TEXT(100)," 
					+ " PicPath TEXT(100),"
					+ " ArCodeOld TEXT(100),"

					+ " ArStatus TEXT(100)," 
					+ " DebtLimitBal TEXT(100),"
					+ " DebtLimit1 TEXT(100)," 
					+ " DebtAmount TEXT(100),"
					+ " IsPromotion TEXT(100),"

					+ " IsRound TEXT(100)," 
					+ " FFCashier TEXT(100),"
					+ " BrokerCode TEXT(100)," 
					+ " BrokerName TEXT(100),"
					+ " BrokerStatus TEXT(100),"

					+ " ContractPerson TEXT(100)," 
					+ " IsCredit TEXT(100),"
					+ " IsCreditItem TEXT(100)," 
					+ " CreditItemDay TEXT(100),"
					+ " PaymentMethod TEXT(100),"

					+ " PaymentTerms TEXT(100),"
					+ " PaymentMethodDesc TEXT(100),"
					+ " PaymentTermsDesc TEXT(100)," 
					+ " CarBandOID TEXT(100),"
					+ " VehicleRegistration TEXT(100),"

					+ " CarBandName TEXT(100),"
					+ " CarGenerationOID TEXT(100)," 
					+ " CarType TEXT(100),"
					+ " ColorOID TEXT(100)," 
					+ " CarSize TEXT(100),"

					+ " CarGenerationName TEXT(100)," 
					+ " ColorName TEXT(100),"
					+ " CarGroup TEXT(100)," 
					+ " CarGroupName TEXT(100),"

					+ " UserModify TEXT(100)," 
					+ " CreateTime TEXT(100),"
					+ " LastUpdate TEXT(100)," 
					+ " StatusUpdate TEXT(100),"
					+ " IsFavorite TEXT(100));");

		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ArGroupMain
					+ "(ArGroupMainOID TEXT(100) PRIMARY KEY,"
					+ " ArGroupMainCode TEXT(100),"
					+ " ArGroupMainDesc TEXT(100));");
		} catch (Exception e) {

		}

		try {
			db.execSQL("CREATE TABLE " + TABLE_ArGroup
					+ "(ArGroupOID TEXT(100) PRIMARY KEY,"
					+ " ArGroupCode TEXT(100)," + " ArGroupDesc TEXT(100),"
					+ " ArGroupMainCode TEXT(100),"
					+ " ArGroupMainDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_Addresses
					+ "(AddressesOID TEXT(100) PRIMARY KEY,"
					+ " AddressCode TEXT(100)," + " ProvinceName TEXT(100),"
					+ " Type TEXT(100)," + " ProvinceCode TEXT(100),"
					+ " IsFavorite TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_CarGroup
					+ "(CarGroupOID TEXT(100) PRIMARY KEY,"
					+ " CarGroupName TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_CarBands
					+ "(CarBandsOID TEXT(100) PRIMARY KEY,"
					+ " CarBandCode TEXT(100)," + " CarBandName TEXT(100),"
					+ " CarBandDescription TEXT(100)," + " OrderNo TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_CarGenerations
					+ "(CarGenerationsOID TEXT(100) PRIMARY KEY,"
					+ " CarGenerationCode TEXT(100),"
					+ " CarGenerationName TEXT(100),"
					+ " CarBandOID TEXT(100)," + " CarSize TEXT(100),"
					+ " CarGroupOID TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_Colors
					+ "(ColorsOID TEXT(100) PRIMARY KEY,"
					+ " ColorCode TEXT(100)," + " ColorName TEXT(100));");
		} catch (Exception e) {

		}

		try {
			db.execSQL("CREATE TABLE " + TABLE_Item
					+ "(ItemOID TEXT(100) PRIMARY KEY,"

					+ " ItemCode TEXT(100)," 
					+ " ItemName TEXT(100),"
					+ " ItemEngName TEXT(100)," 
					+ " ItemDesc TEXT(100),"
					+ " ItemPicPath TEXT(100),"

					+ " ItemUnitCode TEXT(100)," 
					+ " ItemUnitDesc TEXT(100),"
					+ " AltItemUnitCode TEXT(100),"
					+ " AltItemUnitDesc TEXT(100),"
					+ " ItemGroupOID TEXT(100),"

					+ " ItemGroupCode TEXT(100)," 
					+ " ItemGroupDesc TEXT(100),"
					+ " ItemGroupMainOID TEXT(100),"
					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100),"

					+ " IsMainRawPart TEXT(100),"
					+ " ItemWeightingStatus TEXT(100),"
					+ " StdPrice TEXT(100)," 
					+ " AvgWeightAltUnit TEXT(100),"
					+ " ItemRawAverageWeight TEXT(100),"

					+ " BrandOID TEXT(100)," 
					+ " BrandCode TEXT(100),"
					+ " BrandName TEXT(100)," 
					+ " ItemSizeCode TEXT(100),"
					+ " ItemSizeName TEXT(100),"

					+ " ModelOID TEXT(100)," 
					+ " ModelCode TEXT(100),"
					+ " ModelName TEXT(100)," 
					+ " MinStock TEXT(100),"
					+ " StockOut TEXT(100),"

					+ " Cost TEXT(100)," 
					+ " IsReplaceItem TEXT(100),"
					+ " ReplaceItemPacketCode TEXT(100),"
					+ " ReItemName TEXT(100)," 
					+ " BPItemCode1 TEXT(100),"

					+ " BPItemCode2 TEXT(100)," 
					+ " BomDocCode TEXT(100),"
					+ " ApName TEXT(100)," 
					+ " IsStock TEXT(100),"
					+ " IsGeneric TEXT(100),"

					+ " IsBOM TEXT(100)," 
					+ " IsPurchase TEXT(100),"
					+ " IsSold TEXT(100)," 
					+ " IsDiscontinued TEXT(100),"
					+ " BarCodeUPC TEXT(100),"

					+ " BarCode2D TEXT(100)," 
					+ " Duration TEXT(100),"
					+ " LastBuyPrice TEXT(100)," 
					+ " IsPromotion TEXT(100),"
					+ " PromotionStartDate TEXT(100),"

					+ " PromotionEndDate TEXT(100));");

		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemGroup
					+ "(ItemGroupOID TEXT(100) PRIMARY KEY,"

					+ " ItemGroupCode TEXT(100)," + " ItemGroupDesc TEXT(100),"
					+ " ItemGroupMainCode TEXT(100),"

					+ " ItemGroupMainDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemGroupMain
					+ "(ItemGroupMainOID TEXT(100) PRIMARY KEY,"

					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemPrice
					+ "(ItemPriceOID TEXT(100) PRIMARY KEY,"

					+ " CarTypeOID TEXT(100)," 
					+ " CarSizeOID TEXT(100),"
					+ " CarGroupOID TEXT(100)," 
					+ " ItemOID TEXT(100),"
					+ " ItemPrice TEXT(100),"

					+ " CarGroupName TEXT(100)," 
					+ " CarSizeName TEXT(100),"
					+ " CarTypeName TEXT(100)," 
					+ " ItemCode TEXT(100),"
					+ " ItemName TEXT(100),"

					+ " ItemGroupCode TEXT(100)," 
					+ " ItemGroupDesc TEXT(100),"
					+ " ItemGroupOID TEXT(100),"
					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100),"

					+ " ItemGroupMainOID TEXT(100),"
					+ " ItemUnitDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemPromotionPrices
					+ "(ItemPromotionPricesOID TEXT(100) PRIMARY KEY,"

					+ " ItemCode TEXT(100)," + " ItemName TEXT(100),"
					+ " ItemGroupCode TEXT(100)," + " ItemGroupDesc TEXT(100),"
					+ " ItemGroupOID TEXT(100),"

					+ " ItemGroupMainCode TEXT(100),"
					+ " ItemGroupMainDesc TEXT(100),"
					+ " ItemGroupMainOID TEXT(100),"
					+ " ItemUnitDesc TEXT(100)," + " ItemPrice TEXT(100),"

					+ " PromotionType TEXT(100)," + " PromotionMode TEXT(100),"
					+ " PromotionDiscountPrice TEXT(100),"
					+ " PromotionDiscountPercent TEXT(100),"
					+ " ItemPromotionPrice TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemSize
					+ "(ItemSizeOID TEXT(100) PRIMARY KEY,"

					+ " ItemSizeCode TEXT(100)," + " ItemSizeName TEXT(100),"
					+ " ContainSize TEXT(100)," + " ItemGroupCode TEXT(100),"
					+ " ItemGroupDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemStock
					+ "(ItemStockOID TEXT(100) PRIMARY KEY,"

					+ " ItemStockDesc TEXT(100));");
		} catch (Exception e) {

		}
		try {
			db.execSQL("CREATE TABLE " + TABLE_ItemUnit
					+ "(ItemUnitOID TEXT(100) PRIMARY KEY,"

					+ " ItemUnitCode TEXT(100)," + " ItemUnitDesc TEXT(100));");

		} catch (Exception e) {

		}

		Log.d("CREATE TABLE", "Create Table Successfully.");
	}

	public void Clear_table() {
		SQLiteDatabase db;
		db = this.getWritableDatabase();
		onUpgrade(db, 0, 1);
	}

	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FFUSERGROUP);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FFUSER);

		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ArFF);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ArGroupMain);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ArGroup);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_Addresses);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CarGroup);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CarBands);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CarGenerations);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_Colors);

		db.execSQL("DROP TABLE IF EXISTS " + TABLE_Item);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemGroup);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemGroupMain);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemPrice);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemPromotionPrices);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemSize);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemStock);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ItemUnit);
//
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ServicePOS);
////		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ServicePOSDetail);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ServicePOSLists);

		// Re Create on method onCreate
		onCreate(db);
	}

}
